テクサポと学ぶ PostgreSQL #2 ユーザー(1)
2023.12.21この記事はアノテーション株式会社 AWS Technical Support Advent Calendar 2023 21 日目の記事です。
アノテーション、Postgre 大好き村上です。
12 月 17 日に投稿した テクサポと学ぶ PostgreSQL #1 導入 では、PostgreSQL について基本的な説明と EC2 への PostgreSQL 15 のインストールをしてみました。シリーズ第 2 回目は、デフォルトユーザー postgres に対して PostgreSQL ログイン時のパスワードを設定します。
本シリーズでしていくこと
PostgreSQL に対しての基本的な知識を確認しながら、時には EC2 上にインストールした PostgreSQL 15 において DDL や DML 等のコマンドを実行して動作確認をしていきます。
また、業務で RDS for PostgreSQL や Aurora PostgreSQL に対してのお問い合わせに対応しているので、設定や運用面でお役に立つような情報も合わせてご紹介していきます。
デフォルトユーザー postgres とは
RPM パッケージを利用して PostgreSQL をインストールした場合に作成されるデフォルトユーザー postgres は、データベース管理システムにおいて特別なユーザーです。以下は、デフォルトユーザー postgres に関する主な特徴です。
スーパーユーザー権限: デフォルトユーザー postgres は、スーパーユーザー権限を持ちデータベースクラスタ内のすべてのデータベースとオブジェクトに対する制御権を持っています。これにはデータベースの作成、削除、ユーザーの管理、権限の付与などが含まれます。
PostgreSQL 初回接続時のユーザー: PostgreSQL への初めての接続に利用するユーザーです。その後、他のユーザーを作成し、必要に応じてアクセス権を付与します。
通常のオペレーションでは利用しない: デフォルトユーザー postgres は非常に強力な権限を持つため、慎重に扱う必要があります。AWS の利用においても、通常のオペレーションでルートユーザーを利用しないのと同じです。適切なセキュリティ対策が必要で、必要な場合にのみ利用します。
パスワードの設定: 初回ログイン時にパスワードを設定する、定期的にパスワードを変更することが推奨されます。※本記事で実際にやってみます。
データベースクラスタの制御: デフォルトユーザー postgres は、データベースクラスタ全体を管理するため、データベースの停止、再起動、バックアップの作成など、クラスタ全体に関わる操作を実行できます。
パスワードを設定してみる
現在は、OS 側のユーザー postgres として psql を利用するとパスワードが無い状態で PostgreSQL へログインできてしまうので、一般ユーザーを作成する前にデフォルトユーザー postgres に対して PostgreSQL ログイン時のパスワードを設定します。
ALTER ROLE
例）ロールのパスワードを変更します。
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
postgres=# alter role postgres with password 'secret'; ALTER ROLE
これで、デフォルトユーザー postgres を利用する場合でも、パスワードの入力が必要になりました。一度、PostgreSQL からログアウトして、デフォルトユーザー postgres でログインする際にパスワードの入力が求められるかを確認してみます。
postgres=# \q
psql のメタコマンド \q で、PostgreSQL からログアウトしました。再度、psql ツールを利用して PostgreSQL に対して local 接続を実施します。
[postgres@ip-172-31-43-29 ~]$ psql psql (15.4)
アレレ、psql ツールを利用して再度ログインした際に、パスワードを入力すること無く PostgreSQL へログインできてしまいました、、、、
デフォルトユーザー postgres にパスワードが設定されているか pg_authid カタログを確認してみます。
pg_authidカタログはデータベース認証識別子（ロール）の情報を保持します。 ロールは「ユーザ」と「グループ」の概念を包括しています。
postgres=# select * from pg_authid;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |
rolpassword | rolvaliduntil
------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------
--------------------------------------------------------------------------------------------------------------------------------+---------------
6171 | pg_database_owner | f | t | f | f | f | f | f | -1 |
|
6181 | pg_read_all_data | f | t | f | f | f | f | f | -1 |
|
6182 | pg_write_all_data | f | t | f | f | f | f | f | -1 |
|
3373 | pg_monitor | f | t | f | f | f | f | f | -1 |
|
3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 |
|
3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 |
|
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 |
|
4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 |
|
4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 |
|
4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 |
|
4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 |
|
4544 | pg_checkpoint | f | t | f | f | f | f | f | -1 |
|
10 | postgres | t | t | t | t | t | t | t | -1 | SCRAM-
SHA-256$4096:QQbCN9haeLSKeNO3Y72H8Q==$tOcIu7jBcT1WvYK3qO+tdzY1iemUXvq8ILFCmzhnujU=:unFc14sohy+TOyKOzmYT3eGXR4RZnVdoS600kTZ55IQ= |
(13 rows)
デフォルトユーザー postgres に対して、パスワードが設定されていることを確認できました。
ユーザー postgres で local 接続した際に、認証方式としてパスワード認証が適用されるようになっているのか、PostgreSQL への接続ルールが記述されている pg_hba.conf ファイルの確認をしてみます。
$ sudo cat /var/lib/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident
local 接続の認証方式が peer のままでした。現在の状況は、デフォルトユーザー postgres に対してパスワードを設定しているものの、最も優先される接続ルールにおいて local からの peer によるログインが可能な設定となっていました。
pg_hba.conf
peer
クライアントのオペレーティングシステムにおけるユーザ名をオペレーティングシステムから取得し、ユーザ名が要求されたデータベースユーザ名と一致するか検査します。 これはローカル接続の時にのみ使用可能です。
local 接続する際に、パスワードの入力が必須となるよう pg_hba.conf ファイルを以下のように編集します。
$ sudo cat /var/lib/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all scram-sha-256 # IPv4 local connections: host all all 127.0.0.1/32 ident # IPv6 local connections: host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 ident host replication all ::1/128 ident
ファイル内容の変更を反映させる為、以下のコマンドを OS 上で実行して設定ファイルを再読み込みします。
$ sudo systemctl reload postgresql
pg_hba.confファイルは起動時と、主サーバプロセスがSIGHUPシグナルを受け取った時に読み込まれます。 稼働中のシステムでファイルを編集した場合は、（pg_ctl reloadの使用、SQL関数のpg_reload_conf()の呼び出し、またはkill -HUPを使用して）postmasterにファイルをもう一度読み込むようにシグナルを出さなければなりません。
再度、OS ユーザー postgres から psql ツールを利用してログインしてみます。
[postgres@ip-172-31-5-222 ~]$ psql Password for user postgres: psql (15.4) Type "help" for help. postgres=#
デフォルトユーザー postgres で local 接続する場合でも、ちゃんとパスワード入力が求められるようになりました！
まとめ
ユーザーに対してパスワードを設定しても、必ずしもパスワード認証が適用されるわけでは無いというところがとても勉強になりました。
本シリーズを通して、PostgreSQL の基本的な設定や機能を一緒に学んでいければ嬉しいです。
この記事がどなたかのお役に立てば幸いです。
![[レポート] Aurora を使用した費用対効果の高い RAG ベース生成 AI アプリ構築について議論するチョークトーク「Build a cost-effective RAG-based gen AI application with Amazon Aurora [REPEAT]」 #DAT313-R1 #AWSreInvent](https://images.ctfassets.net/ct0aopd36mqt/3IQLlbdUkRvu7Q2LupRW2o/edff8982184ea7cc2d5efa2ddd2915f5/reinvent-2024-sessionreport-jp.jpg)
